package railRoad.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import railRoad.Ticket;
import railRoad.dao.interfaces.IDAO;
import railRoad.db.DBConectionPool;
import railRoad.exception.DBException;

/**
 * Implementation of {@link#IDAO} for {@link#Ticket}
 * 
 * @see IDAO
 * @see Ticket
 * @author Rukin
 *
 */
public class TicketDAO implements IDAO<Ticket> {

	DBConectionPool conectionPool = DBConectionPool.getInstance();

	public Ticket select(int id) throws DBException {
		Ticket ticket = null;
		Connection connection = null;
		try {
			connection = conectionPool.getConnection();
			connection.setAutoCommit(false);

			Statement statement = connection.createStatement();
			ResultSet resultSet = statement
					.executeQuery("SELECT * FROM TICKET where TICKET_ID = "
							+ id);
			int tId = -1;
			int ttId = -1;
			int pId = -1;
			int rId = -1;
			double price = 0;

			while (resultSet.next()) {
				tId = resultSet.getInt("TICKET_ID");
				ttId = resultSet.getInt("TICKET_TYPE_ID");
				pId = resultSet.getInt("PASSANGER_ID");
				rId = resultSet.getInt("ROUTE_ID");
				price = resultSet.getDouble("Price");
			}

			ticket = new Ticket(tId, pId, rId, ttId, price);
		} catch (SQLException e) {
			throw new DBException(e.getMessage());
		} finally {
			conectionPool.close(connection);
		}// finally

		return ticket;
	}

	public Ticket select(String field) throws DBException {
		return null;
	}

	public int insert(Ticket ticket, int parrentId) throws DBException {
		Connection connection = null;
		int result = -1;
		try {
			connection = conectionPool.getConnection();
			connection.setAutoCommit(false);

			PreparedStatement preparedStatement = connection
					.prepareStatement("INSERT INTO TICKET( PASSANGER_ID , ROUTE_ID , TICKET_TYPE_ID , PRICE ) VALUES (?,?,?,?)");

			preparedStatement.setInt(1, ticket.getPassenger_id());
			preparedStatement.setInt(2, ticket.getRoute_id());
			preparedStatement.setInt(3, ticket.getType());
			preparedStatement.setDouble(4, ticket.getPrice());

			result = preparedStatement.executeUpdate();

			ResultSet resultSet = connection.createStatement().executeQuery(
					"SELECT MAX(TICKET_ID) as ID FROM TICKET");
			int id = -1;
			while (resultSet.next()) {
				id = resultSet.getInt("ID");
			}

			ticket.setTicket_id(id);

			connection.commit();

		} catch (SQLException e) {
			throw new DBException(e.getMessage());
		} finally {
			conectionPool.close(connection);
		}// finally

		return result;
	}

	public int update(Ticket ticket) throws DBException {
		Connection connection = null;

		int rezult = -1;
		try {
			connection = conectionPool.getConnection();
			connection.setAutoCommit(false);

			PreparedStatement preparedStatement = connection
					.prepareStatement("UPDATE TICKET T SET "
							+ "T.PASSANGER_ID = ?, T.TICKET_TYPE_ID  = ?, T.ROUTE_ID = ?, T.PRICE = ? "
							+ "WHERE T.TICKET_ID = ?");

			preparedStatement.setInt(1, ticket.getPassenger_id());
			preparedStatement.setInt(2, ticket.getType());
			preparedStatement.setInt(3, ticket.getRoute_id());
			preparedStatement.setDouble(4, ticket.getPrice());
			preparedStatement.setInt(5, ticket.getTicket_id());

			rezult = preparedStatement.executeUpdate();

			connection.commit();
		} catch (SQLException e) {
			throw new DBException(e.getMessage());
		} finally {
			conectionPool.close(connection);
		}// finally

		return rezult;
	}

	public int delete(Ticket ticket) throws DBException {
		Connection connection = null;

		int result = -1;
		try {
			connection = conectionPool.getConnection();
			connection.setAutoCommit(false);

			PreparedStatement preparedStatement = connection
					.prepareStatement("delete from ticket where ticket_id = ?");

			preparedStatement.setInt(1, ticket.getTicket_id());

			result = preparedStatement.executeUpdate();

			connection.commit();
		} catch (SQLException e) {
			throw new DBException(e.getMessage());
		} finally {
			conectionPool.close(connection);
		}// finally

		return result;
	}//delete

}
